********************************************************************************************************************************
***   Replication file for:                                                                                                  ***
***   Berbee, P., Braun, S. T. and Franke, R. (2024). Reversing Fortunes of German Regions, 1926-2019. JoEG.			     ***
***   							                                                                                             ***
***   SCRIPT: 	_x4_preparation_spatialcontrols.do																			 ***	
***   PURPOSE: 	Reads in and prepares data on spatial/geographic characteristics										 	 ***
********************************************************************************************************************************

* Preamble (unnecessary when executing run.do)
run "$reversing/scripts/programs/_config.do"

************
* Code begins
************

*** Import data

clear
import excel "$reversing/data/SpatialControls.xlsx", sheet("Data") firstrow clear
rename Sheet1__La labor_market_id
drop A IDchr Memel-Troppau TownPop

** Assign the average of the neighboring LLMs as the soil quality measure for the two LLMs with missing values
sum SoilQ if inlist(labor_market_id, 148, 160, 161)
replace SoilQ = r(mean) if labor_market_id == 159
sum SoilQ if inlist(labor_market_id, 150, 156, 157, 160)
replace SoilQ = r(mean) if labor_market_id == 158

** Rename and label

rename D longitude 
label var longitude "Longitude"
rename lat latitude 
label var latitude "Latitude"
rename Area area
label var area "Area (sq km)"
rename DistCa dist_carbon
label var dist_carbon "Distance to carboniferous strata (km)"
rename SoilQ soil_quality
label var soil_quality "Soil Quality"
rename Elevat elevation
label var elevation "Elevation (m)"
rename Rugged ruggedness
label var ruggedness "Ruggedness"
rename DistGGB dist_GGborder
label var dist_GGborder "Distance to the inner-German border"
rename DistRiver dist_river
label var dist_river "Distance to nearest major river"
rename DistCoast dist_coast
label var dist_coast "Distance to coast (km)"
rename Temp9120 temp9120
replace temp9120=temp9120/10
label var temp9120 "Average temperature (°C) 1991-2020"
rename SumDays9120 hotdays9120
label var hotdays9120 "Annual average of days >25°C (1991-2020)"
rename Sun9120 sun9120
label var sun9120 "Annual average of sunhours (1991-2020)"
rename Remote_KS2014 remote_KS2014
label var remote_KS2014 "Remoteness within Germany (Keller and Shiue, 2014)"


gen redevelopment_area = 0
replace redevelopment_area = 1 if (ZonenRand54 == 1) | (WirtNotstand54 == 1) | (LandNotstand54 == 1) | (LandNotstandKrieg54 == 1)
label var redevelopment_area "Redevelopment areas (0/1)"
drop ZonenRand54 WirtNotstand54 LandNotstand54 LandNotstandKrieg54

rename DistEB dist_Eborder 
label var dist_Eborder "Distance to West Germany's eastern border"
rename DistWB dist_Wborder 
label var dist_Wborder "Distance to West Germany's western border"

gen state_1834_id = 1 if Pol1834 == "Baden"
replace state_1834_id = 2 if Pol1834 == "Bayern"
replace state_1834_id = 3 if Pol1834 == "Hannover"
replace state_1834_id = 4 if Pol1834 == "Hessen-Darmstadt"
replace state_1834_id = 5 if Pol1834 == "Oldenburg"
replace state_1834_id = 6 if Pol1834 == "Preußen"
replace state_1834_id = 8 if Pol1834 == "Rest"
replace state_1834_id = 7 if Pol1834 == "Württemberg"

label def pol34l 1 "Baden" 2 "Bavaria" 3 "Hanover" 4 "Hesse-Darmstadt" 5 "Oldenburg" 6 "Prussia" 7 "Wurttemberg" 8 "Others" 
label values state_1834_id pol34l
label var state_1834_id "German states 1834"

rename Zollverein1834 zollverein_1834
label var zollverein_1834 "Member of the Zollverein in 1834 (0/1)?"


*** Save data

drop RailL RoadL Pol1834

sort labor_market_id

save "$reversing/processed/intermediate/spatialcontrols.dta",replace



